#include "databasespecificmanager.h"
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QVariant>
#include <QMap>
#include <QJsonObject>
#include <QJsonDocument>
#include <QJsonArray>

DatabaseSpecificManager::DatabaseSpecificManager(QObject *parent)
    : QObject(parent)
{
}

void DatabaseSpecificManager::setDatabase(const QSqlDatabase &db)
{
    m_database = db;
    m_databaseType = db.driverName().toUpper();
    
    // 根据数据库类型加载特定功能
    if (m_databaseType == "QSQLITE") {
        loadSQLiteSpecifics();
    } else if (m_databaseType == "QMYSQL") {
        loadMySQLSpecifics();
    } else if (m_databaseType == "QPSQL") {
        loadPostgreSQLSpecifics();
    } else if (m_databaseType == "QOCI") {
        loadOracleSpecifics();
    } else if (m_databaseType == "QODBC") {
        loadSQLServerSpecifics();
    }
}

QStringList DatabaseSpecificManager::getFunctions() const
{
    QStringList functions;
    
    if (!m_database.isOpen()) {
        return functions;
    }
    
    QString queryStr;
    if (m_databaseType == "QSQLITE") {
        queryStr = "SELECT name FROM sqlite_master WHERE type='function'";
    } else if (m_databaseType == "QMYSQL") {
        queryStr = "SHOW FUNCTION STATUS WHERE Db = DATABASE()";
    } else if (m_databaseType == "QPSQL") {
        queryStr = "SELECT proname FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = 'public'";
    } else if (m_databaseType == "QOCI") {
        queryStr = "SELECT OBJECT_NAME FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION'";
    } else if (m_databaseType == "QODBC") {
        // SQL Server
        queryStr = "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'";
    }
    
    if (!queryStr.isEmpty()) {
        QSqlQuery query(m_database);
        if (query.exec(queryStr)) {
            while (query.next()) {
                functions.append(query.value(0).toString());
            }
        }
    }
    
    return functions;
}

QStringList DatabaseSpecificManager::getProcedures() const
{
    QStringList procedures;
    
    if (!m_database.isOpen()) {
        return procedures;
    }
    
    QString queryStr;
    if (m_databaseType == "QSQLITE") {
        // SQLite doesn't have stored procedures
        return procedures;
    } else if (m_databaseType == "QMYSQL") {
        queryStr = "SHOW PROCEDURE STATUS WHERE Db = DATABASE()";
    } else if (m_databaseType == "QPSQL") {
        queryStr = "SELECT proname FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = 'public'";
    } else if (m_databaseType == "QOCI") {
        queryStr = "SELECT OBJECT_NAME FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'PROCEDURE'";
    } else if (m_databaseType == "QODBC") {
        // SQL Server
        queryStr = "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'";
    }
    
    if (!queryStr.isEmpty()) {
        QSqlQuery query(m_database);
        if (query.exec(queryStr)) {
            while (query.next()) {
                procedures.append(query.value(0).toString());
            }
        }
    }
    
    return procedures;
}

QStringList DatabaseSpecificManager::getTriggers() const
{
    QStringList triggers;
    
    if (!m_database.isOpen()) {
        return triggers;
    }
    
    QString queryStr;
    if (m_databaseType == "QSQLITE") {
        queryStr = "SELECT name FROM sqlite_master WHERE type='trigger'";
    } else if (m_databaseType == "QMYSQL") {
        queryStr = "SHOW TRIGGERS";
    } else if (m_databaseType == "QPSQL") {
        queryStr = "SELECT tgname FROM pg_trigger t JOIN pg_class c ON t.tgrelid = c.oid WHERE c.relname NOT LIKE 'pg_%'";
    } else if (m_databaseType == "QOCI") {
        queryStr = "SELECT TRIGGER_NAME FROM USER_TRIGGERS";
    } else if (m_databaseType == "QODBC") {
        // SQL Server
        queryStr = "SELECT name FROM sys.triggers";
    }
    
    if (!queryStr.isEmpty()) {
        QSqlQuery query(m_database);
        if (query.exec(queryStr)) {
            while (query.next()) {
                triggers.append(query.value(0).toString());
            }
        }
    }
    
    return triggers;
}

QStringList DatabaseSpecificManager::getViews() const
{
    QStringList views;
    
    if (!m_database.isOpen()) {
        return views;
    }
    
    QString queryStr;
    if (m_databaseType == "QSQLITE") {
        queryStr = "SELECT name FROM sqlite_master WHERE type='view'";
    } else if (m_databaseType == "QMYSQL") {
        queryStr = "SHOW FULL TABLES WHERE Table_type = 'VIEW'";
    } else if (m_databaseType == "QPSQL") {
        queryStr = "SELECT viewname FROM pg_views WHERE schemaname = 'public'";
    } else if (m_databaseType == "QOCI") {
        queryStr = "SELECT VIEW_NAME FROM USER_VIEWS";
    } else if (m_databaseType == "QODBC") {
        // SQL Server
        queryStr = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS";
    }
    
    if (!queryStr.isEmpty()) {
        QSqlQuery query(m_database);
        if (query.exec(queryStr)) {
            while (query.next()) {
                views.append(query.value(0).toString());
            }
        }
    }
    
    return views;
}

QVariantMap DatabaseSpecificManager::getTableIndexes(const QString &tableName) const
{
    QVariantMap indexes;
    
    if (!m_database.isOpen()) {
        return indexes;
    }
    
    QString queryStr;
    if (m_databaseType == "QSQLITE") {
        queryStr = QString("PRAGMA index_list(%1)").arg(escapeIdentifier(tableName));
    } else if (m_databaseType == "QMYSQL") {
        queryStr = QString("SHOW INDEX FROM %1").arg(escapeIdentifier(tableName));
    } else if (m_databaseType == "QPSQL") {
        queryStr = QString("SELECT indexname, indexdef FROM pg_indexes WHERE tablename = '%1'").arg(tableName);
    } else if (m_databaseType == "QOCI") {
        queryStr = QString("SELECT INDEX_NAME, COLUMN_NAME FROM USER_IND_COLUMNS WHERE TABLE_NAME = '%1'").arg(tableName);
    } else if (m_databaseType == "QODBC") {
        // SQL Server
        queryStr = QString("sp_helpindex %1").arg(escapeIdentifier(tableName));
    }
    
    if (!queryStr.isEmpty()) {
        indexes = executeQuery(queryStr);
    }
    
    return indexes;
}

QVariantMap DatabaseSpecificManager::getTableConstraints(const QString &tableName) const
{
    QVariantMap constraints;
    
    if (!m_database.isOpen()) {
        return constraints;
    }
    
    QString queryStr;
    if (m_databaseType == "QSQLITE") {
        queryStr = QString("PRAGMA table_info(%1)").arg(escapeIdentifier(tableName));
    } else if (m_databaseType == "QMYSQL") {
        queryStr = QString("SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = '%1'").arg(tableName);
    } else if (m_databaseType == "QPSQL") {
        queryStr = QString("SELECT conname, contype FROM pg_constraint c JOIN pg_class r ON c.conrelid = r.oid WHERE r.relname = '%1'").arg(tableName);
    } else if (m_databaseType == "QOCI") {
        queryStr = QString("SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME = '%1'").arg(tableName);
    } else if (m_databaseType == "QODBC") {
        // SQL Server
        queryStr = QString("SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = '%1'").arg(tableName);
    }
    
    if (!queryStr.isEmpty()) {
        constraints = executeQuery(queryStr);
    }
    
    return constraints;
}

QVariantMap DatabaseSpecificManager::getTableForeignKeys(const QString &tableName) const
{
    QVariantMap foreignKeys;
    
    if (!m_database.isOpen()) {
        return foreignKeys;
    }
    
    QString queryStr;
    if (m_databaseType == "QSQLITE") {
        queryStr = QString("PRAGMA foreign_key_list(%1)").arg(escapeIdentifier(tableName));
    } else if (m_databaseType == "QMYSQL") {
        queryStr = QString("SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '%1' AND REFERENCED_TABLE_NAME IS NOT NULL").arg(tableName);
    } else if (m_databaseType == "QPSQL") {
        queryStr = QString("SELECT conname, confrelid::regclass, pn.nspname AS parentschema FROM pg_constraint c JOIN pg_class r ON c.conrelid = r.oid JOIN pg_class pr ON c.confrelid = pr.oid JOIN pg_namespace pn ON pr.relnamespace = pn.oid WHERE r.relname = '%1' AND c.contype = 'f'").arg(tableName);
    } else if (m_databaseType == "QOCI") {
        queryStr = QString("SELECT CONSTRAINT_NAME, R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = '%1' AND CONSTRAINT_TYPE = 'R'").arg(tableName);
    } else if (m_databaseType == "QODBC") {
        // SQL Server
        queryStr = QString("SELECT COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id WHERE OBJECT_NAME(f.parent_object_id) = '%1'").arg(tableName);
    }
    
    if (!queryStr.isEmpty()) {
        foreignKeys = executeQuery(queryStr);
    }
    
    return foreignKeys;
}

QString DatabaseSpecificManager::getTableDDL(const QString &tableName) const
{
    QString ddl;
    
    if (!m_database.isOpen()) {
        return ddl;
    }
    
    if (m_databaseType == "QSQLITE") {
        QSqlQuery query(m_database);
        query.prepare("SELECT sql FROM sqlite_master WHERE type='table' AND name=?");
        query.addBindValue(tableName);
        if (query.exec() && query.next()) {
            ddl = query.value(0).toString();
        }
    } else if (m_databaseType == "QMYSQL") {
        QSqlQuery query(m_database);
        query.prepare("SHOW CREATE TABLE " + escapeIdentifier(tableName));
        if (query.exec() && query.next()) {
            ddl = query.value(1).toString();
        }
    } else if (m_databaseType == "QPSQL") {
        QSqlQuery query(m_database);
        query.prepare("SELECT pg_get_viewdef('" + tableName + "')");
        if (query.exec() && query.next()) {
            ddl = query.value(0).toString();
        }
    }
    
    return ddl;
}

QVariantMap DatabaseSpecificManager::getDatabaseSizeInfo() const
{
    QVariantMap sizeInfo;
    
    if (!m_database.isOpen()) {
        return sizeInfo;
    }
    
    QString queryStr;
    if (m_databaseType == "QSQLITE") {
        // SQLite数据库大小可以通过文件系统获取
        // 这里简单返回空，实际应用中可能需要通过其他方式获取
    } else if (m_databaseType == "QMYSQL") {
        queryStr = "SELECT table_schema AS database_name, SUM(data_length + index_length) AS size FROM information_schema.tables GROUP BY table_schema";
    } else if (m_databaseType == "QPSQL") {
        queryStr = "SELECT pg_database_size(current_database()) AS size";
    }
    
    if (!queryStr.isEmpty()) {
        sizeInfo = executeQuery(queryStr);
    }
    
    return sizeInfo;
}

QString DatabaseSpecificManager::getDatabaseVersion() const
{
    QString version;
    
    if (!m_database.isOpen()) {
        return version;
    }
    
    QSqlQuery query(m_database);
    if (m_databaseType == "QSQLITE") {
        query.prepare("SELECT sqlite_version()");
    } else if (m_databaseType == "QMYSQL") {
        query.prepare("SELECT VERSION()");
    } else if (m_databaseType == "QPSQL") {
        query.prepare("SELECT version()");
    } else if (m_databaseType == "QOCI") {
        query.prepare("SELECT * FROM v$version WHERE banner LIKE 'Oracle%'");
    } else if (m_databaseType == "QODBC") {
        query.prepare("SELECT @@VERSION");
    }
    
    if (query.exec() && query.next()) {
        version = query.value(0).toString();
    }
    
    return version;
}

QVariantMap DatabaseSpecificManager::getDatabaseParameters() const
{
    QVariantMap parameters;
    
    if (!m_database.isOpen()) {
        return parameters;
    }
    
    QString queryStr;
    if (m_databaseType == "QSQLITE") {
        queryStr = "PRAGMA compile_options";
    } else if (m_databaseType == "QMYSQL") {
        queryStr = "SHOW VARIABLES";
    } else if (m_databaseType == "QPSQL") {
        queryStr = "SELECT name, setting FROM pg_settings";
    }
    
    if (!queryStr.isEmpty()) {
        parameters = executeQuery(queryStr);
    }
    
    return parameters;
}

bool DatabaseSpecificManager::executeManagementCommand(const QString &command, QVariantMap &result)
{
    if (!m_database.isOpen()) {
        return false;
    }
    
    QSqlQuery query(m_database);
    if (!query.exec(command)) {
        result["error"] = query.lastError().text();
        return false;
    }
    
    result = executeQuery(command);
    return true;
}

void DatabaseSpecificManager::loadSQLiteSpecifics()
{
    m_databaseInfo["type"] = "SQLite";
}

void DatabaseSpecificManager::loadMySQLSpecifics()
{
    m_databaseInfo["type"] = "MySQL";
}

void DatabaseSpecificManager::loadPostgreSQLSpecifics()
{
    m_databaseInfo["type"] = "PostgreSQL";
}

void DatabaseSpecificManager::loadOracleSpecifics()
{
    m_databaseInfo["type"] = "Oracle";
}

void DatabaseSpecificManager::loadSQLServerSpecifics()
{
    m_databaseInfo["type"] = "SQL Server";
}

QVariantMap DatabaseSpecificManager::executeQuery(const QString &query) const
{
    QVariantMap result;
    
    if (!m_database.isOpen()) {
        return result;
    }
    
    QSqlQuery sqlQuery(m_database);
    if (sqlQuery.exec(query)) {
        QSqlRecord record = sqlQuery.record();
        QJsonArray rows;
        
        while (sqlQuery.next()) {
            QJsonObject row;
            for (int i = 0; i < record.count(); ++i) {
                row[record.fieldName(i)] = sqlQuery.value(i).toString();
            }
            rows.append(row);
        }
        
        result["data"] = rows;
    } else {
        result["error"] = sqlQuery.lastError().text();
    }
    
    return result;
}

QString DatabaseSpecificManager::escapeIdentifier(const QString &identifier) const
{
    // 基本的标识符转义，实际应用中可能需要更复杂的处理
    if (m_databaseType == "QSQLITE" || m_databaseType == "QPSQL") {
        return "\"" + identifier + "\"";
    } else if (m_databaseType == "QMYSQL") {
        return "`" + identifier + "`";
    } else {
        return "[" + identifier + "]";
    }
}